Support for Stored Procedures in Popular Databases
This page last changed on Nov 28, 2008.

Support for Stored Procedures in Popular Databases

Each database vendor approaches stored procedures differently. Oracle Data Service Integrator support limitations generally reflect JDBC driver limitations.

General Restrictions

There are several restrictions that apply to stored procedures generally: 

  • Oracle Data Service Integrator does not support rowset as an input parameter.
  • Only data types supported by Oracle Data Service Integrator can be imported as part of stored procedures.
    For a list of database types supported by Oracle Data Service Integrator XQuery-SQL Mapping Reference

Oracle Stored Procedure Support

The following table describes data service creation support for Oracle stored procedures. 

Term Usage
Procedure types
  • Procedures
  • Functions
  • Packages
Parameter modes
  • Input only
  • Output only
  • Input/Output
  • None
Parameter data types Any Oracle PL/SQL data type except:
  • ROWID
  • UROWID
    When defining function signatures, note that the Oracle %TYPE and %ROWTYPE types must be translated to XQuery types that match the true types underlying the stored procedure's %TYPE and %ROWTYPE declarations. %TYPE declarations map to simple types; %ROWTYPE declarations map to rowset types.
Data returned from a function Oracle supports returning PL/SQL data types such as NUMBER, VARCHAR, %TYPE, and %ROWTYPE as parameters.
Comments The following identifies limitations associated with importing Oracle database procedure metadata.
  • The data service creation process can only detect the data structure for cursors that have a binding PL/SQL record. For a dynamic cursor you need to manually specify the cursor schema.
  • Data from a PL/SQL record structure cannot be retrieved due to an Oracle JDBC driver limitations.
  • The Oracle JDBC driver supports rowset output parameters only if they are defined as reference cursors in a package.
  • The Oracle JDBC driver does not support NATURALN and POSITIVEN as output only parameters.

Sybase Stored Procedure Support

The following table describes data service creation support for Sybase stored procedures. 

Term Usage
Procedure types
  • Procedures
  • Grouped procedures
  • Functions are categorized as a scalar or inline table-valued and multi-statement table-valued function. Inline table-valued and multi-statement table-valued functions return rowsets.
Parameter modes
  • Input only
  • Output only
Parameter data types For a list of database types supported by Oracle Data Service Integrator, see the XQuery-SQL Mapping Reference.
Data returned from a function Sybase functions supports returning a single value or a table. Procedures return data in the following ways:
  • As output parameters, which can return either data (such as an integer or character value).
  • As return codes, which are always an integer value.
  • As a rowset for each SELECT statement contained in the stored procedure or any other stored procedures called by that stored procedure.
  • As a global cursor that can be referenced outside the stored procedure supports, returning single value or multiple values.
Comments The following identifies limitations associated with importing Sybase database procedure metadata:
  • The Sybase JDBC driver does not support input/output or output only parameters that are rowsets (including cursor variables).
  • The Jconnect driver and some versions of the Oracle Sybase driver cannot detect the parameter mode of the procedure. In such a case, the return mode will be UNKNOWN, preventing importation of the metadata. To proceed, you need to set the correct mode.

IBM DB2 Stored Procedure Support

The following table describes data service creation support for IBM DB2 stored procedures.

Term Usage
Procedure types
  • Procedures
  • Functions
  • Packages where each function is also categorized as a scalar, column, row, or table function.
    Here are additional details on function categorization:
  • A scalar function returns a single-valued answer each time it is called.
  • A column function is one which conceptually is passed a set of like values (a column) and returns a single-valued answer (AVG( )).
  • A row function is a function that returns one row of values.
  • A table function is a function that returns a table to the SQL statement that referenced it.
Parameter modes
  • Input only
  • Output only
  • Input/output
Parameter data types For a list of database types supported by Oracle Data Service Integrator see the XQuery-SQL Mapping Reference.  For a list of database types supported by Oracle Data Service Integrator, see the XQuery-SQL Mapping Reference.
Data returned from a function DB2 supports returning a single value, a row of values, or a table.
Comments The following identifies limitations associated with creating physical data services based on DB2 stored procedures:
  • Column type functions are not supported.
  • Rowsets as output parameters are not supported.
  • The DB2 JDBC driver supports float, double, and decimal input only and output only parameters. Float, double, and decimal data types are not supported as input/output parameters.

Microsoft SQL Server Stored Procedure Support

The following table describes data service creation support for Microsoft stored procedures.

Term Usage
Procedure types SQL Server supports procedures, grouped procedures, and functions. Each function is also categorized as a scalar or inline table-valued and multi-statement table-valued function. Inline table-valued and multi-statement table-valued functions return rowsets.
Parameter modes SQL Server supports input only and output only parameters.
Parameter data types SQL Server procedures/functions support any SQL Server data type as a parameter.  For a list of database types supported by Oracle Data Service Integrator, see the XQuery-SQL Mapping Reference.
Data returned from a function SQL Server functions supports returning a single value or a table. Data can be returned in the following ways:
  • As output parameters, which can return either data (such as an integer or character value) or a cursor variable (cursors are rowsets that can be retrieved one row at a time).
  • As return codes, which are always an integer value.
  • As a rowset for each SELECT statement contained in the stored procedure or any other stored procedures called by that stored procedure.
Comments The following identifies limitations associated with importing SQL Server procedure metadata.
  • Result sets returned from SQL server (as well as those returned from Sybase) are not detected automatically. Instead you will need to manually add parameters as a result.
  • The Microsoft SQL Server JDBC driver does not support rowset input/output or output only parameters (including cursor variables).
Document generated by Confluence on Jan 13, 2009 15:57